﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Reports.Util
{
    public class Common
    {
        /// <summary>
        /// сумма товара на складе на конкретную дату
        /// </summary>
        /// <param name="date"></param>
        /// <param name="ConnectionString"></param>
        /// <returns></returns>
        public static decimal RestToDate(string ConnectionString, DateTime date)
        {
            string QueryString1 =
@"WITH DATA(PartyId,[SUM])
AS
(
SELECT  PartyId, SUM(Qntty)
FROM         dbo.vwGoodsFlow
WHERE     DocDate <= @DocDate
GROUP BY PartyId
HAVING      (SUM(Qntty) > 0)
)
select 
sum(d.[sum] * p.RetailPrice)
from DATA d INNER JOIN CD.Parties p
ON d.PartyId = p.Id";
            string QueryString =
@"SELECT sum(RetailSum)
FROM dbo.vwGoodsFlow WHERE DocDate < @DocDate";
            decimal res = decimal.Zero;
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DocDate = new SqlParameter("@DocDate", SqlDbType.DateTime);
                    DocDate.Value = Reports.Util.DTConvert.RoundDate(date);
                    command.Parameters.Add(DocDate);
                    connection.Open();
                    object o = command.ExecuteScalar();
                    if (!Convert.IsDBNull(o))
                        res = Convert.ToDecimal(o);                    
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
            return res;
        }
        /// <summary>
        /// получаем имя Аптеки
        /// </summary>
        /// <param name="ConnectionString"></param>
        /// <returns></returns>
        public static string GetAptekaName(string ConnectionString)
        {
            string result = string.Empty;
            string QueryString = 
@"SELECT TOP (1) Name
FROM CA.Contragents
WHERE (Id = 0)";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    connection.Open();
                    object o = command.ExecuteScalar();
                    if (!Convert.IsDBNull(o))
                        result = (string)o;
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
            return result;
        }
        public static string DeptName
        {
            get
            {
                string tmp = System.Configuration.ConfigurationSettings.AppSettings["DeptName"];
                if (string.IsNullOrEmpty(tmp))
                    return "Департамент";
                return tmp;
            }
        }
        public static string HostName
        {
            get
            {
                string tmp = System.Configuration.ConfigurationManager.AppSettings["hostName"];
                
                if (string.IsNullOrEmpty(tmp))
                {
                    throw new Exception("Задайте hostName в файле конфинурации");                    
                }
                return tmp;
            }
        }
    }
}
